﻿using Microsoft.SqlServer.Server;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace EnqueteMobile.Repository.Base.SqlTools
{
    public static class TempTable
    {
        private static Dictionary<Type, string> typeToSqlDbTypeMap = GetTypeToSqlDbTypeMap();

        /// <summary> Create temporary table in database </summary>
        /// <param name="sqlTransaction">Connection with database</param>
        /// <param name="dataTable">Datatable object containing data for temporary table</param>
        public static void Create(SqlTransaction sqlTransaction, DataTable dataTable)
        {
            if (dataTable.TableName.StartsWith("#") == false)
            {
                throw new ArgumentException(string.Format("The TableName property on the received DataTable does not represent a temporary table name. TableName: {0}.", dataTable.TableName.TrimEnd()));
            }

            ICollection<string> columns = new List<string>();

            foreach (DataColumn column in dataTable.Columns)
            {
                string columnType = typeToSqlDbTypeMap[column.DataType];
                columns.Add(new StringBuilder().Append(column.ColumnName).Append(" ").Append(columnType).ToString());
            }

            string columnString = string.Join(",", columns);

            StringBuilder scriptBuilder = new StringBuilder();
            scriptBuilder.AppendFormat("CREATE TABLE {0} ({1})", dataTable.TableName, columnString);

            string createScript = scriptBuilder.ToString();

            SqlCommand tempTableCommand = new SqlCommand(createScript, sqlTransaction.Connection, sqlTransaction);
            tempTableCommand.ExecuteNonQuery();
        }

        /// <summary> Drop temporary table in database </summary>
        /// <param name="sqlTransaction">Connection with database</param>
        /// <param name="temporaryTableName">Name of temporary table</param>
        /// <exception cref="System.ArgumentException">
        /// If table isn't a temporary table, thow exception
        /// </exception>
        public static void Drop(SqlTransaction sqlTransaction, string temporaryTableName)
        {
            if (temporaryTableName.StartsWith("#") == false)
            {
                throw new ArgumentException(string.Format("The tableName property received does not represent a temporary table name. tableName: {0}.", temporaryTableName));
            }

            string dropScript = new StringBuilder("DROP TABLE ").Append(temporaryTableName).ToString();

            SqlCommand tempTableCommand = new SqlCommand(dropScript, sqlTransaction.Connection, sqlTransaction);
            tempTableCommand.ExecuteNonQuery();
        }

        /// <summary> Mapping C# type to T-SQL type </summary>
        /// <returns></returns>
        private static Dictionary<Type, string> GetTypeToSqlDbTypeMap()
        {
            Dictionary<Type, string> typeToSqlDbTypeMap = new Dictionary<Type, string>();
            typeToSqlDbTypeMap.Add(typeof(Int64), SqlDbType.BigInt.ToString());
            typeToSqlDbTypeMap.Add(typeof(Byte[]), SqlDbType.Binary.ToString());
            typeToSqlDbTypeMap.Add(typeof(Boolean), SqlDbType.Bit.ToString());
            typeToSqlDbTypeMap.Add(typeof(Char[]), new StringBuilder(SqlDbType.Char.ToString()).Append("(100)").ToString());
            typeToSqlDbTypeMap.Add(typeof(DateTime), SqlDbType.DateTime.ToString());
            typeToSqlDbTypeMap.Add(typeof(DateTimeOffset), SqlDbType.DateTimeOffset.ToString());
            typeToSqlDbTypeMap.Add(typeof(Decimal), new StringBuilder(SqlDbType.Decimal.ToString()).Append("(32,6)").ToString());
            typeToSqlDbTypeMap.Add(typeof(Double), SqlDbType.Float.ToString());
            typeToSqlDbTypeMap.Add(typeof(Int32), SqlDbType.Int.ToString());
            typeToSqlDbTypeMap.Add(typeof(String), new StringBuilder(SqlDbType.VarChar.ToString()).Append("(MAX)").ToString());
            typeToSqlDbTypeMap.Add(typeof(Single), SqlDbType.Real.ToString());
            typeToSqlDbTypeMap.Add(typeof(Int16), SqlDbType.SmallInt.ToString());
            typeToSqlDbTypeMap.Add(typeof(TimeSpan), SqlDbType.Time.ToString());
            typeToSqlDbTypeMap.Add(typeof(Guid), SqlDbType.UniqueIdentifier.ToString());

            return typeToSqlDbTypeMap;
        }
    }
}